package com.example.test.repository;

import com.example.test.model.Order;
import com.example.test.model.User;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.Collections;
import java.util.List;

@Repository
public class JdbcUserRepository implements UserRepository {
    private final JdbcTemplate jdbcTemplate;

    public JdbcUserRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void save(User user) {
        String sql = "INSERT INTO \"user\" (name, username, password, created_at, updated_at) " +
                "VALUES (?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) RETURNING id, created_at, updated_at";

        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});
            ps.setString(1, user.getName());
            ps.setString(2, user.getUsername());
            ps.setString(3, user.getPassword());
            return ps;
        }, keyHolder);

        user.setId((Long) keyHolder.getKeyList().get(0).get("id"));
        user.setCreatedAt(((Timestamp) keyHolder.getKeyList().get(0).get("created_at")).toLocalDateTime());
        user.setUpdatedAt(((Timestamp) keyHolder.getKeyList().get(0).get("updated_at")).toLocalDateTime());
    }

    @Override
    public User findById(long id) {
        String sql = "SELECT * FROM \"user\" WHERE id = ?";

        try {
            return jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(User.class), id);
        } catch (IncorrectResultSizeDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<User> find(int offset, int limit) {
        String sql = "SELECT * FROM \"user\" ORDER BY id LIMIT ? OFFSET ?";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(User.class), limit, offset);
    }

    @Override
    public List<Order> findRecentOrdersByUserId(long userId) {
        // TODO
        return Collections.emptyList();
    }

    @Override
    public void update(User user) {
        String sql = "UPDATE \"user\" SET name = ?, username = ?, password = ?, updated_at = CURRENT_TIMESTAMP " +
                "WHERE id = ? RETURNING created_at, updated_at";

        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, new String[]{"created_at", "updated_at"});
            ps.setString(1, user.getName());
            ps.setString(2, user.getUsername());
            ps.setString(3, user.getPassword());
            ps.setLong(4, user.getId());
            return ps;
        }, keyHolder);

        user.setCreatedAt(((Timestamp) keyHolder.getKeyList().get(0).get("created_at")).toLocalDateTime());
        user.setUpdatedAt(((Timestamp) keyHolder.getKeyList().get(0).get("updated_at")).toLocalDateTime());
    }

    @Override
    public void deleteById(long id) {
        String sql = "DELETE FROM \"user\" WHERE id = ?";
        jdbcTemplate.update(sql, id);
    }
}
